/**
 * Chesney's Fireplace System Includes New Supplier, New Fireplace Item, Supplier and Fireplace Update, 
 * Supplier and Fireplace Reports, Login, main menu and Tutorial.
 * 
 * It also Includes Database connection, Database Extension and the two classes which are MySupplier and My Fireplace.
 *  
 * Copyright (C) Bournemouth University 
 * 
 * @author Ruth Shepperd, Ari Lopez
 * 
 * @version 1.0
 */
package Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.swing.DefaultComboBoxModel;
import javax.swing.JFrame;
import javax.swing.JOptionPane;

import Database.Database;
import Database.MyFireplace;
/**
* Class DatabaseExtended.
* This Class will be used by other classes to implement its methods to
* operate with a database
* Declared as abstract because it is a superclass, no objects can be created from it.
* This Class uses MySupplier and MyFireplace Classes to add, update and retrieve
* data from the database .
* 
* @author Ari Lopez and Ruth Shepperd
* 
* @version 1.0
* 
*/
public abstract class DatabaseExtended {

	/**
	 * Connect to Database and Check the User Credentials
	 * 
	 * @param UserName
	 *            (String)
	 * @param UserPassword
	 *            (String)
	 * 
	 *            Get the Query result
	 * @return True or False (as a boolean)
	 */
	public static boolean checkUserCredentials(String UserName,
			String UserPassword) {

		Database.connect();

		// SQL SELECT Query
		String query = String.format(
				"SELECT * FROM User where UserName='%s' and UserPassword='%s'",
				UserName, UserPassword);

		String[][] result = Database.returnQuery(query);
		Database.close();

		if (result[0].length > 0) {
			// Database.printQuery(result);
			// System.out.println("YES");
			return true;
		}
		return false;
	}

	/**
	 * Get the fireplace from MyFireplace String Array
	 * 
	 * @return fireplaces (from MyFireplace)
	 */
	public static MyFireplace[] getFireplaces() {

		// SQL SELECT Query
		String[][] result = Database.returnQuery("select * from fireplace");

		Database.printQuery(result);

		MyFireplace fireplaces[] = new MyFireplace[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			fireplaces[y] = new MyFireplace(data);
			// System.out.println(fireplaces[y].getFireplaceID());
		}

		return fireplaces;
	}

	/**
	 * Connect to Database and Save the fireplace
	 * 
	 * @param fireplace
	 *            (MyFireplace)
	 */
	public static void saveFireplace(MyFireplace fireplace) {

		fireplace.setSupplierID(fireplace.getSupplierID().replaceAll("\n", "")
				.trim());

		Database.connect();
		// SQL INSERT
		String insert = "insert into fireplace values (null,'"
				+ fireplace.getFireplaceName() + "','"
				+ fireplace.getDescription() + "','" + fireplace.getFinish()
				+ "','" + fireplace.getStyle() + "','"
				+ fireplace.getItemPrice() + "','"
				+ fireplace.getStockQuantity() + "','"
				+ fireplace.getSupplierID() + "')";

		// System.out.println("This is an insert " + insert);
		Database.doSQL(insert);
		Database.close();
		// System.out.println("inserting Fireplace");

	}

	/**
	 * Connect to Database and update the fireplace
	 * 
	 * @param fireplace
	 *            (MyFireplace)
	 */
	public static void updateFireplace(MyFireplace fireplace) {

		fireplace.setFireplaceID(fireplace.getFireplaceID()
				.replaceAll("\n", "").trim());

		Database.connect();

		// SQL UPDATE
		String update = "update fireplace set Fireplace_Name='"
				+ fireplace.getFireplaceName() + "',Description='"
				+ fireplace.getDescription() + "',Finish='"
				+ fireplace.getFinish() + "',Style='" + fireplace.getStyle()
				+ "',Item_price='" + fireplace.getItemPrice()
				+ "',Stock_quantity='" + fireplace.getStockQuantity()
				+ "',Supplier_ID='" + fireplace.getSupplierID()
				+ "' Where Fireplace_ID='" + fireplace.getFireplaceID() + "'";

		// System.out.println("This is an update " + update);
		Database.doSQL(update);
		Database.close();
		// System.out.println("Amending Fireplace");

	}

	/**
	 * Connect to Database and delete the fireplace
	 * 
	 * @param fireplaceID
	 *            (int)
	 */
	public static void deleteFireplace(int fireplaceID) {

		Database.connect();

		// SQL DELETE
		String delete = "delete from fireplace where Fireplace_ID='"
				+ fireplaceID + "'";

		// System.out.println("This is a delete " + delete);
		Database.doSQL(delete);
		Database.close();
		// System.out.println("Deleting Fireplace");

	}

	/**
	 * Get the fireplace name from the fireplace table
	 * 
	 * @return groupName (as an ArrayList <String>)
	 * @throws SQLException
	 */
	public static ArrayList<String> fireplace() throws SQLException {
		// Create an array list to be filled with group names
		ArrayList<String> groupNames = new ArrayList<String>();

		// SQL SELECT Query
		String query = "SELECT fireplace_name FROM fireplace";

		Statement stmt = null;
		ResultSet rs = stmt.executeQuery(query);

		while (rs.next()) {
			String groupName = rs.getString("fireplace_name");
			// add group names to the array list
			groupNames.add(groupName);

		}
		rs.close();
		return groupNames;
	}

	/**
	 * Connect to Database and get the suppliers
	 * 
	 * @return suppliers (from MySupplier)
	 */
	public static MySupplier[] getSuppliers() {

		Database.connect();
		// SQL SELECT Query
		String[][] result = Database.returnQuery("SELECT * FROM Supplier");
		Database.close();
		MySupplier suppliers[] = new MySupplier[result[0].length];
		String data[] = new String[result.length];
		for (int y = 0; y < result[0].length; y++) {
			for (int x = 0; x < result.length; x++) {
				data[x] = result[x][y];
			}
			suppliers[y] = new MySupplier(data);
			// System.out.println(suppliers[y].getSupplierID());
		}

		return suppliers;
	}

	/**
	 * Connect to Database and Save the supplier
	 * 
	 * @param supplier
	 *            (MySupplier)
	 */
	public static void saveSupplier(MySupplier supplier) {

		if (supplier.getSupplierID() != null) {
			supplier.setSupplierID(supplier.getSupplierID()
					.replaceAll("\n", "").trim());
		} else {
			// System.out.println("NULL");
		}

		// System.out.println("This is the Supplier ID: " +
		// supplier.getSupplierID());
		MySupplier suppliers[] = getSuppliers();
		String id = "";
		for (MySupplier s : suppliers) {
			System.out
					.println("These are the supplier ids in the database" + s);
			s.setSupplierID(s.getSupplierID().replaceAll("\n", "").trim());
			if ((s.getSupplierID().equals(supplier.getSupplierID()))) {
				id = s.getSupplierID();
				// System.out.println("This is the Supplier ID: " + id);
			}
		}

		id = id.replaceAll("\n", "").trim();
		Database.connect();
		// SQL INSERT
		Database.doSQL(String
				.format("INSERT INTO Supplier VALUES (null,'%s','%s','%s','%s','%s','%s');",
						supplier.getSupplierName(), supplier.getAddress(),
						supplier.getTown(), supplier.getPostcode(),
						supplier.getTelephoneNo(), supplier.getEmail()));

		Database.close();
		// System.out.println("Creating Supplier");
	}

	/**
	 * Connect to Database and update the supplier
	 * 
	 * @param supplier
	 *            (MySupplier)
	 */
	public static void updateSupplier(MySupplier supplier) {

		if (supplier.getSupplierID() != null) {
			supplier.setSupplierID(supplier.getSupplierID()
					.replaceAll("\n", "").trim());
		} else {
			// System.out.println("NULL");
		}

		// System.out.println("This is the Supplier ID: " +
		// supplier.getSupplierID());
		MySupplier suppliers[] = getSuppliers();
		String id = "";
		for (MySupplier s : suppliers) {
			// System.out.println("These are the supplier ids in the database" +
			// s);
			s.setSupplierID(s.getSupplierID().replaceAll("\n", "").trim());
			if ((s.getSupplierID().equals(supplier.getSupplierID()))) {

				id = s.getSupplierID();
				// System.out.println("This is the Supplier ID: " + id);
			}
		}
		// SQL UPDATE
		String update = "update Supplier set Supplier_Name='"
				+ supplier.getSupplierName() + "',Address='"
				+ supplier.getAddress() + "',Town='" + supplier.getTown()
				+ "',Postcode='" + supplier.getPostcode()
				+ "',Telephone_Number='" + supplier.getTelephoneNo()
				+ "',Email='" + supplier.getEmail() + "'where Supplier_ID='"
				+ supplier.getSupplierID() + "'";

		// System.out.println("This is an update " + update);
		Database.connect();
		Database.doSQL(update);
		Database.close();
		// System.out.println("Amending Customer");
	}

	/**
	 * Connect to Database and delete the supplier
	 * 
	 * @param supplier
	 *            (MySupplier)
	 */
	public static void deleteSupplier(MySupplier supplier) {

		if (supplier.getSupplierID() != null) {
			supplier.setSupplierID(supplier.getSupplierID()
					.replaceAll("\n", "").trim());
			Database.connect();
			// SQL DELETE
			Database.doSQL(String.format(
					"DELETE FROM Supplier WHERE Supplier_ID='%s';",
					supplier.getSupplierID()));
		} else {
			// System.out.println("NULL");
		}
		Database.close();
		// System.out.println(supplier.getSupplierID() + "has been deleted");

	}
}
